<?php

	/* description	: wrap all database helper and function operation, powerful performance by php mysql native driver extension
	 * developer	: BREEZEER
	 * release date	: 2022.05.13
	 * version		: 1.0.0
	 *
	 *
	 */
    namespace App\common;

    use Illuminate\Support\Facades\DB;
    use Illuminate\Support\Facades\Redis;
    use function PHPUnit\Framework\isNull;
	
	/*
	 *
	 */
	class cDBException extends \Exception
	{
	}
    class cDB 
	{     
		public function __destruct()
		{
			unset($this->m_rs);
			unset($this->m_ar);
		}
		public function autoConvertValueToNumberOrString($v)
		{
			return app()->gcHelper->gfAutoConvertValueToNumberOrString($v);
		}

        public function getAllRowsData(){
			
            $arx			= [];
			$ar				= is_array($this->m_rs)?$this->m_rs:$arx;foreach($ar as $one_o)
			{
				# auto convert value to number or string
				#
				$one_row	= get_object_vars($one_o);foreach($one_row as $k => $v){$one_row[$k] = $this->autoConvertValueToNumberOrString($v);}
				$arx[]		= $one_row;
			}

			# free memory
			#
			$this->m_ar		= $arx;unset($arx);$arx=null;
			return $this->m_ar;
        }
		public function mergeArrayMultiValueToSingle($a=[])
		{
			return	(	
						new class($a,$this)
						{
							public function __destruct	()
							{
								unset($this->ax);
							}
							public function __construct	($a_first,$thisx)
							{
								$this->ax		= $a_first	;
								$this->cDB		= $thisx	;
							}
							public function merge($b)
							{
								$a			= $this->ax;
								$this->ax	= array_merge($a,$b);

								# free memory immediately
								#
								unset($a);unset($b);
								
								#
								#
								return $this;
							}
							public function getLastSingleArray()
							{
								return $this->ax;
							}
						}
					);
		}
		public function buildSqlMultiParameter($ar)
		{
			$arx						= [];foreach($ar as $v){$arx[]='?';}
			$o							= (object)	[
														'parameter'				=> implode(',',$arx	)	,
														'value'					=> $ar					,
														'thisx'					=> $this				,
														'cDB'					=> $this				,
													];
			return $o;
		}
		public function buildSqlWhereWithConditionsAndValue(){

			# SQL where condition builder
			#
			$ox_build_where = (
								new class ($this) 
								{
									public function __construct($thisx)
									{
										$this->m_cDB					= $thisx				;
										$this->m_ar_where_bind_value	= []					;
										$this->m_str_sql_where			= '1=1/* placeholder */';
									}
									public function appendWhereFilterContidion($where_filter_condition,$where_bind_value)
									{
										$this->m_str_sql_where				= $this->m_str_sql_where . $where_filter_condition		; # one where filter condition
										
										#
										#
										if(is_array($where_bind_value))
										{
											$this->m_ar_where_bind_value	= $this->merge($where_bind_value)->getLastSingleArray();
										}
										else
										{
											$this->m_ar_where_bind_value[]	= $where_bind_value										; # map bind where value
										}

										#
										#
										return $this;
									}
									public function merge($ar=[])
									{
										return $this->m_cDB->mergeArrayMultiValueToSingle($this->m_ar_where_bind_value)->merge($ar);
									}
									public function getAllCombineBuildStandarSQL()
									{
										return app()->gcHelper->gfConvertArrayToStdClass	([
																								'cDB'							=> $this->m_cDB					,
																								'thisx'							=> $this						,
																								'sql_where'						=> $this->m_str_sql_where		,
																								'sql_where_bind_value_array'	=> $this->m_ar_where_bind_value	,
																							]);
									}
								}
							);
						
			return $ox_build_where;
		}
		public function formatPagingStyle(&$ar)
		{
			$ar =	[[
						'total'			=> $this->m_ar['page_offset_info']['total_row_count']	, # 总条数
						'current_page'	=> $this->m_ar['page_offset_info']['page_index']		, # 第几页
						'total_page'	=> $this->m_ar['page_offset_info']['total_page_size']	, # 总页数
						'data'			=> $this->m_ar['content']								,
					]];
			return $this;
		}
        public function forEachRow($callback_one_row=null,&$p_ar_ex=[]/* reference output value */,$debug=false)
		{
            # return all array directly
            #
            #
            if($callback_one_row==null)
			{ 
                return $this->getAllRowsData();
            }

            # create an empty content node
            #
            #
            $p_ar_ex['content'] = array();

            # row data class 
            #
            #
            $ox =	(
						new class($this)
						{
							public function __construct($thisx)
							{
								$this->thisx				= $thisx;
							}
							public function set ($o)
							{ 
								$this->append_item			= array()	; # will be apppend item
								$this->remove_item_by_key	= array()	; # default value => do not remove any item
								$this->o					= $o		; # database one row data
								return $this;                                   
							}
							public function get ($k)
							{
								$v							= @$this->o->$k;    
								$v							= in_array($v,array('null',null))?'':$v;   

								return $this->thisx->autoConvertValueToNumberOrString($v);
							}
							public function __get ($name)
							{
								return $this->get($name);
							}
							public function getOneRowAllData ($append_item=[]/* appdend item*/	) : array  
							{
								# dump all object to array
								#
								#
								$ar = get_object_vars($this->o);

								# append item to current row array
								# 
								# 
								foreach($append_item		as $k => $v){$ar[$k] = $v;}														# append item by callback
								foreach($this->append_item  as $one_append_item){foreach($one_append_item	as $k => $v){$ar[$k] = $v;}}	# append item by class property
								
								# remove
								#
								#
								foreach($this->remove_item_by_key as $one_remove_item){foreach($one_remove_item as $k){unset($ar[$k]);}}

								# auto convert value to number or string type
								#
								#
								foreach($ar as $k => $v){$ar[$k]	= $this->thisx->autoConvertValueToNumberOrString($v);}

								# return all array
								#
								#
								return $ar;
							}

                            /**
                             * 2023-07-08 不使用自动格式替换方法 如合同编号为纯文本字符串超长时候自动转换会导致溢出
                             * @param $append_item
                             * @return array
                             */
							public function getOneRowAllDataNotFmt($append_item=[]/* appdend item*/	) : array
							{
								# dump all object to array
								#
								#
								$ar = get_object_vars($this->o);

								# append item to current row array
								#
								#
								foreach($append_item		as $k => $v){$ar[$k] = $v;}														# append item by callback
								foreach($this->append_item  as $one_append_item){foreach($one_append_item	as $k => $v){$ar[$k] = $v;}}	# append item by class property

								# remove
								#
								#
								foreach($this->remove_item_by_key as $one_remove_item){foreach($one_remove_item as $k){unset($ar[$k]);}}

								# auto convert value to number or string type
								#
								#
                                #
//								foreach($ar as $k => $v){$ar[$k]	= $this->thisx->autoConvertValueToNumberOrString($v);}

								# return all array
								#
								#
								return $ar;
							}
							public function removeItem($remove_item_by_key=[]/* remove item by explicit array key from exist array */)
							{
								$this->remove_item_by_key[]			= $remove_item_by_key;
								return $this;
							}				
							public function appendItem($append_item=[]/* will be append item */)
							{
								$this->append_item[]				= $append_item;
								return $this;
							}
						}
					);

            # append row data to content
            #
            #
            #
            foreach($this->m_rs as $o)
			{
				# $p_ar_ex['content'][] = $callback_one_row($ox->set($o/* one row data object */));
				#
				#
				$one_row = $callback_one_row($ox->set($o/* one row data object */));if ($one_row !=null)
				{
					$p_ar_ex['content'][]							= $one_row;
				}
			}

			#
			#
			#
			$this->m_ar = $p_ar_ex;unset($ox);

			# debug
			#
			#
			if($debug){ print_r($p_ar_ex);die; }

            return $this;
        }	
        public function getRowCount()
		{
			return is_array($this->m_rs)?count($this->m_rs):-1;
		}
		public function getDbOneRow($sql,$ar=[],$debug=false){

            $this->select($sql,$ar,debug:$debug)->getAllRowsData();if($this->getRowCount()>0)
			{
				# force get the first row data only
				#
				$ar	= (array)$this->m_rs[0];
	 
				# auto convert value to number or string
				#
				foreach($ar as $k => $v){$ar[$k] = $this->autoConvertValueToNumberOrString($v);}

				return $ar;
			}
			else
			{
				return [];
			}

		}
		public function getDbValue($sql,$ar=[],$debug=false) 
		{
			if($debug)
			{
				return $this->debugSql($sql,$ar);
			}
			
			$arx = DB::select($sql,$ar);if(count($arx)>0)
			{
				$arv = get_object_vars($arx[0]);foreach($arv as $v)
				{
 					return $this->autoConvertValueToNumberOrString($v==null?'':$v);
				}
			}
			else
			{
				return '';
			}
		}
		private function debugSql($sql,$ar=[])
        {
            # try to get actual running SQL
            #
            try 
            {
                DB::beginTransaction();DB::insert($sql,$ar);DB::rollBack();
            }
            catch (\Exception $exception)
            {
                print_r("Exception  :\n                ".$exception->getMessage());
                print_r("\n");
                print_r("SQL        :\n                ");
            }

            # replace ? to real parameter
			#
			$i=0;$sql=preg_replace_callback (
                                                '/\?/'
                                                ,
                                                function($matches) use(&$i,$ar)
                                                {
                                                    return $ar[$i++];
                                                }
                                                ,
                                                $sql
                                            );
            print_r("".$sql);
			
			# save log to local file
			#
			app()->gcHelper->gfDebugPrintToLocalFileByDefault($sql);
            die;
		}
        public function select($sql,$ar=[],$one_page_size=0/* one page size */,$page_index=1/* page index the offset first index based 1 */,$debug=false/* bool => [true or false] or -1 => return directly */)
		{
			if($page_index==''||$page_index<1)
			{
				$page_index					= 1		;
			}

            $this->m_rs						= null	;
            $this->m_page_offset_info		= null	;
            if($debug===true)
			{
				return $this->debugSql($sql,$ar);
            }
			elseif ($debug===-1)
			{
                return $this;
            }
			else
			{
                # query 
                #
                $this->m_rs					= DB::select($sql,$ar);

				# paging offset
                #
                $total_row_count            = $this->getRowCount();
                $total_page_size            = $one_page_size==0?0:(ceil($total_row_count/$one_page_size)); 
				
				# page index over max page total size ?
				#
				if(($page_index-1) <= $total_page_size)
				{
				}
				else
				{
					$page_index = $total_page_size; 
				}

                $this->m_page_offset_info   =	[
													'total_row_count'       => $total_row_count ,
													'total_page_size'       => $total_page_size ,
													'one_page_size'         => $one_page_size   ,
													'page_index'            => $page_index      ,
												];

                # page offset by array slice
                #
                if($one_page_size>0)
				{
                    $this->m_rs             = array_slice($this->m_rs,($one_page_size*($page_index-1))/* offset position */,$one_page_size/* slice length */);
				}
            }
            return $this;
        }
        public function getPageOffsetInfo(&$ar=[])
		{
			$ar['page_offset_info'] = $this->autoConvertValueToNumberOrString($this->m_page_offset_info);
            return $this;
        }
		
        /* 在数据库表 $table_name 利用数组 $ar_field_and_value [ 字段名 => 值 ] 直接添加一条记录
         *  
         */
        public function insertByArray($table_name,$ar_field_and_value,$debug=false)
		{
            # construct standard insert SQL
            #
            $ar_fields  = array();foreach(array_keys($ar_field_and_value) as $v){$ar_fields [] = '`'.$v.'`';}
            $ar_values  = array_values  ($ar_field_and_value);
            $sql        = "insert into $table_name ( ".implode(",",$ar_fields).") values (?".str_repeat(",?",count($ar_fields)-1).")";
			
            #
            #
			return $this->insert($sql,$ar_values,$debug);
        }
		public function getLastInsertId()
		{
			return $this->m_getLastInsertId;		
		}
        public function insert($sql,$ar=[],$debug=false)
        {
            $this->m_rs                 = null;
            $this->m_getLastInsertId    = 0;

            #
            #
            if($debug===true)
            {
                return $this->debugSql($sql,$ar);
            }
            elseif ($debug===-1)
            {
                return $this;
            }
            else
            {
				try
                {
					#
					#
					DB::beginTransaction();
					$r							= DB::insert($sql,$ar);

					# get last inserted primary key id
					#
					$this->m_getLastInsertId	= $this->getDbValue('SELECT @@IDENTITY ;');
					DB::commit();
				}
                catch(\Exception $ex)
                {
					DB::rollBack();
				}
            }
			return $this;
        }

		/* 用数组键值对直接更新单个表  
		 * 
		 *
		 */
		public function updateByArrayWithWhere($table_name,$ar_where,$ar_field_and_value,$debug=false,$callback_update_ok=null){

			$i			= 0;
			$set_fields	= '';
			$comma		= '';

			# sql set field part
			#
			foreach(array_keys($ar_field_and_value) as $v)
			{
				if($i==0)
				{
					$set_fields = "set ";
				}
				else
				{
					$comma		= ",";
				}
				$set_fields = $set_fields .$comma. '`'.$v.'`=?';
				$i++;
			}
			$ar_values  = array_values  ($ar_field_and_value);

			# sql where condition part
			#
			if(count($ar_where)==0)
			{
			}
			else
			{
				# where
				#
				$where		= $ar_where[0];

				# where bind value
				#
				foreach($ar_where[1] as $v){$ar_values[] = $v;}
			}
			
			# standard update SQL
			#
			$sql = "update ".$table_name ." ". $set_fields." ".$where;

			#
			#
			return $this->update($sql,$ar_values,$debug,$callback_update_ok);
		}
        public function update($sql,$ar=[],$debug=false,$callback_update_ok = null)
		{
            $this->m_rs = null;
            if($debug===true)
			{
				return $this->debugSql($sql,$ar);
            }
			elseif ($debug===-1)
			{
				return $this;
            }
			else
			{
				try
				{
					DB::beginTransaction();$flag = DB::update($sql,$ar);DB::commit();if(!is_null($callback_update_ok))
					{
						return $flag?$callback_update_ok(true):$callback_update_ok(false);
					}
				}
				catch(\Exception $ex)
				{
					DB::rollBack();
				}
            }
           return $this;
        }

        public function delete($sql,$ar=[],$debug=false)
		{
            $this->m_rs     = null;
            if($debug===true)
            {
				return $this->debugSql($sql,$ar);
            }
            elseif ($debug===-1)
            {
                return $this;
            }
            else
            {
				try
                {
					DB::beginTransaction();DB::delete($sql,$ar);DB::commit();
				}
                catch(\Exception $ex)
                {
					DB::rollBack();
				}
            }
           return $this;
        }
    }
